import database_connecter
import openpyxl


def insert_pal2db():
    # 打开Excel文件
    wb = openpyxl.load_workbook('../data/幻兽帕鲁配种表.xlsx')
    sheet = wb['全配种表']

    mydb = database_connecter.db_connector()

    headers_row = sheet[1]
    for i in range(2, sheet.max_row + 1):
        cell_str = headers_row[i - 1].value
        my_cursor = mydb.cursor()
        str = get_chinese_and_other(cell_str)

        # 插入数据
        sql = "INSERT INTO pw_pal (code, name) VALUES (%s, %s)"
        val = (str[0], str[1])
        my_cursor.execute(sql, val)
    # 提交更改
    mydb.commit()
    # 关闭连接
    mydb.close()


def get_chinese_and_other(str_cell):
    # 分离中文字符串和其他字符串
    chinese_chars = ""
    other_chars = ""
    for char in str_cell:
        if '\u4e00' <= char <= '\u9fff':  # 判断是否为中文字符
            chinese_chars += char
        else:
            other_chars += char
    result = [chinese_chars, other_chars]
    return result
